﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using TracNghiemTinHocDaiCuong.Common;
using TracNghiemTinHocDaiCuong.ThucThe;

namespace TracNghiemTinHocDaiCuong.DAO
{
    public class CauHoiDAO
    {

        public static IList<CauHoi> TimKiem(string noiDung, string range)
        {
            string query = string.Format("SELECT * " +
                                            "FROM CauHoi ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@NoiDung", noiDung)
            };
            IList<CauHoi> danhSachCauHoi = new List<CauHoi>();
            if (noiDung != "")
            {
                query += string.Format("WHERE NoiDung LIKE N'%'+@NoiDung+'%' ");
                return DanhSachCauHoi(danhSachCauHoi, query, parameter);
            }
            if (noiDung == "")
            {
                query += string.Format("WHERE NoiDung LIKE @Range ");
                var par = new Collection<SqlParameter>
                {
                    new SqlParameter("@Range", range)
                };
                return DanhSachCauHoi(danhSachCauHoi, query, par);
            }
            else
                return DanhSachCauHoi(danhSachCauHoi, query, parameter);
        }

        public static IList<CauHoi> DanhSachCauHoi(IList<CauHoi> danhSachCauHoi,
            string query, Collection<SqlParameter> parameter)
        {
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            while (read.Read())
            {
                danhSachCauHoi.Add(DoiTuong(read));
            }
            return danhSachCauHoi;
        }

        public static CauHoi TimKiemTheoId(int idCauHoi)
        {
            string query = string.Format("SELECT * FROM CauHoi WHERE MaCH=@MaCH");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaCH", idCauHoi)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            CauHoi CauHoi = new CauHoi();
            while (read.Read())
            {
                CauHoi = DoiTuong(read);
            }
            return CauHoi;
        }

        public static CauHoi DoiTuong(IDataRecord read)
        {
            return new CauHoi
            {
                MaCH = Methods.ConvertNumber(read["MaCH"].ToString()),
                NoiDung = Methods.ConvertString(read["NoiDung"].ToString()),
                DapAnA = Methods.ConvertString(read["DapAnA"].ToString()),
                DapAnB = Methods.ConvertString(read["DapAnB"].ToString()),
                DapAnC = Methods.ConvertString(read["DapAnC"].ToString()),
                DapAnD = Methods.ConvertString(read["DapAnD"].ToString()),
                MaCD = Methods.ConvertNumber(read["MaCD"].ToString()),
                DoKho = Methods.ConvertString(read["DoKho"].ToString()),
                DapAn = Methods.ConvertString(read["DapAn"].ToString())
            };
        }
        

        public static IList<CauHoi> LayDanhSach()
        {
            IList<CauHoi> danhSach = new List<CauHoi>();
            string query = string.Format("SELECT * FROM CauHoi");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while(read.Read())
            {
                danhSach.Add(DoiTuong(read));
            }
            return danhSach;
        }

        public static bool Them(CauHoi cauHoi)
        {
            string query = string.Format("INSERT INTO CauHoi(NoiDung, DapAnA, DapAnB, " +
                "DapAnC, DapAnD, MaCD, DoKho, DapAn) VALUES(@NoiDung, @DapAnA, @DapAnB, @DapAnC, @DapAnD, " + 
                "@MaCD, @DoKho, @DapAn)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@NoiDung", cauHoi.NoiDung),
                new SqlParameter("@DapAnA", cauHoi.DapAnA),
                new SqlParameter("@DapAnB", cauHoi.DapAnB),
                new SqlParameter("@DapAnC", cauHoi.DapAnC),
                new SqlParameter("@DapAnD", cauHoi.DapAnD),
                new SqlParameter("@MaCD", cauHoi.MaCD),
                new SqlParameter("@DoKho", cauHoi.DoKho),
                new SqlParameter("@DapAn", cauHoi.DapAn)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Sua(CauHoi cauHoi)
        {
            string query = string.Format("UPDATE CauHoi SET NoiDung=@NoiDung, DapAnA=@DapAnA, DapAnB=@DapAnB, " +
            "DapAnC=@DapAnC, DapAnD=@DapAnD, MaCD=@MaCD, DoKho=@DoKho, DapAn=@DapAn WHERE MaCH=@MaCH");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@NoiDung", cauHoi.NoiDung),
                new SqlParameter("@DapAnA", cauHoi.DapAnA),
                new SqlParameter("@DapAnB", cauHoi.DapAnB),
                new SqlParameter("@DapAnC", cauHoi.DapAnC),
                new SqlParameter("@DapAnD", cauHoi.DapAnD),
                new SqlParameter("@MaCD", cauHoi.MaCD),
                new SqlParameter("@DoKho", cauHoi.DoKho),
                new SqlParameter("@MaCH", cauHoi.MaCH), 
                new SqlParameter("@DapAn", cauHoi.DapAn)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Xoa(int idCauHoi)
        {
            string query = string.Format("DELETE FROM CauHoi WHERE MaCH=@MaCH");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaCH", idCauHoi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }
    }
}